import pandas as pd
from EasyReportCode.easy.base.DbHelper import selectTable

# exe_date=str(time.strftime('%Y-%m-%d',time.localtime(time.time()-86400)))
# exe_date = '2022-06-19'

class Province_City_Info():
    def __init__(self, exe_date):
        self.exe_date = exe_date

    def __call__(self):
        province_city_info={'city_id':'','province_id':'','city':'','province':''}
        table_info=[
            {"table":"aggr_5gsa_video_ciservice_day_%s","dsn":'SQLITE_VS'},
            {"table":"aggr_video_ci_day_%s","dsn":'SQLITE_VS'},
            {"table":"aggr_volte_call_ci_day_%s","dsn":'SQLITE_VS'},
            ]

        sql="""select a.province_id,a.city_id,b.province_name,b.city_name
                                from(
                                    select province_id,city_id,count(1) cnt from 
                                    %s
                                    where province_id<>0 and city_id<>0
                                    group by province_id,city_id
                                    order by cnt desc
                                    limit 1
                                )a left join 
                                (
                                    select province_id,city_id,province_name,city_name from dim_comm_city
                                )b
                                on a.province_id=b.province_id and a.city_id=b.city_id"""
        for ti in table_info:
            try:
                table=ti['table']%self.exe_date.replace('-','')
                dsn=ti['dsn']
                #print(dsn,sql%table)
                df=selectTable(dsn, sql % table)
                province_city_info['city_id']=[i for i in df['city_id'].values][0]
                province_city_info['province_id']=[i for i in df['province_id'].values][0]
                province_city_info['city']=[i for i in df['city_name'].values][0]
                province_city_info['province']=[i for i in df['province_name'].values][0]
                break
            except Exception as e:
                print("""error. get city_province info error.
                        %s
                        %s
                        %s"""%(dsn,sql%table,e))
                pass
        return province_city_info


def get_pc_info():
    exe_date = '2022-06-19'
    return Province_City_Info(exe_date)()

def GetDateList(startdate,enddate):
    return [i.strftime('%Y-%m-%d') for i in pd.date_range(startdate, enddate)]

# if __name__ == '__main__':
#     get_pc_info()